package com.ease.document.poi;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFName;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.util.CellRangeAddressList;

import com.ease.common.util.DateFormatUtil;
import com.ease.common.util.Detect;

/**
 * 
 * @author nathanleewei
 * 
 */
public abstract class WorkbookUtil extends Detect {

	/**
	 *
	 */
	private static final transient Log log = LogFactory.getLog(WorkbookUtil.class);

	public static int setDropDownList(HSSFWorkbook workbook, HSSFSheet sheet, int sheetIndex, int firstRow, int columnIndex, String[] items) {
		int maxRow = SpreadsheetVersion.EXCEL97.getLastRowIndex();// maxRowNum=65535
		return setDropDownList(workbook, sheet, sheetIndex, firstRow, maxRow, columnIndex, columnIndex, items);
	}

	public static int setDropDownList(HSSFWorkbook workbook, HSSFSheet sheet, int lastSheetIndex, int firstRow, int lastRow, int firstColumn, int lastColumn, String[] items) {
		if (!notEmpty(items)) {
			return lastSheetIndex;
		}
		StringBuffer sb = new StringBuffer();
		for (String item : items) {
			sb.append(item);
		}
		DVConstraint dataValidation = null;
		/** EXCEL不支持超过255字数的下拉框 */
		if (sb.toString().length() <= 255) {
			dataValidation = DVConstraint.createExplicitListConstraint(items);
		} else {
			String sheetName = "hidden" + lastSheetIndex;
			HSSFSheet hiddenSheet = workbook.createSheet(sheetName);
			workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet), true);

			for (int i = 0, length = items.length; i < length; i++) {
				String name = items[i];
				HSSFRow row = hiddenSheet.createRow(i);
				HSSFCell cell = row.createCell(0);
				cell.setCellValue(name);
			}

			String nameName = sheetName;
			HSSFName rangeName = workbook.createName();
			rangeName.setRefersToFormula(sheetName + "!$A$1:$A$" + items.length);
			rangeName.setNameName(nameName);

			// 加载下拉列表内容
			dataValidation = DVConstraint.createFormulaListConstraint(nameName);
			lastSheetIndex += 1;
		}
		setDataValidationConstraint(sheet, firstRow, lastRow, firstColumn, lastColumn, dataValidation);
		return lastSheetIndex;
	}

	public static HSSFDataValidation setDataValidationConstraint(HSSFSheet sheet, int firstRow, int lastRow, int firstColumn, int lastColumn, DataValidationConstraint constraint) {
		CellRangeAddressList region = new CellRangeAddressList(firstRow, lastRow, firstColumn, lastColumn);
		HSSFDataValidation dataValidation = new HSSFDataValidation(region, constraint);
		// dataValidation.createErrorBox("输入不合法", "请输入下拉列表中的值。");
		sheet.addValidationData(dataValidation);
		return dataValidation;
	}

	public static HSSFWorkbook getWorkbook(File file) {
		if (null != file) {
			try {
				InputStream is = new FileInputStream(file);
				POIFSFileSystem fs = new POIFSFileSystem(is);
				HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fs);
				return hssfWorkbook;
			} catch (Exception e) {
				if ("Unable to construct record instance".equals(e.getMessage())) {
					// 重新下一个最新的poi的3.1 的包貌似就可以解决了
					// throw new RuntimeException("不支持带有数据有效性校验的Excel文件");
					throw new RuntimeException("Excel file format not supported", e);
				}
			}
		}
		return null;
	}

	public static byte[] marshall(HSSFWorkbook hssfWorkbook) {
		if (null != hssfWorkbook) {
			ByteArrayOutputStream output = null;
			try {
				output = new ByteArrayOutputStream();
				hssfWorkbook.write(output);
				output.flush();
				byte[] byteArray = output.toByteArray();
				return byteArray;
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				if (null != output) {
					try {
						output.close();
					} catch (IOException e) {
						e.printStackTrace();
					}
					output = null;
				}

			}
		}
		return null;
	}

	public static Object getCellObject(HSSFCell cell) {
		if (null != cell) {
			switch (cell.getCellType()) {

			case HSSFCell.CELL_TYPE_BLANK:
				return null;

			case HSSFCell.CELL_TYPE_BOOLEAN:
				return new Boolean(cell.getBooleanCellValue());

			case HSSFCell.CELL_TYPE_NUMERIC:
				Double db = new Double(cell.getNumericCellValue());
				if ((db - db.longValue()) == 0) {
					return new Long(db.longValue());
				}
				// 防止科学计数法问题
				if (db > 9999999) {
					return new Long(db.longValue());
				}
				return db;

			default: // HSSFCell.CELL_TYPE_STRING:
				return getCellString(cell);
			}
		}
		return null;
	}

	public static String getCellAsString(HSSFCell cell) {
		if (null != cell) {
			Object value = getCellObject(cell);
			if (null != value) {
				return String.valueOf(value);
			}
		}
		return null;
	}

	public static String getCellString(HSSFCell cell) {
		if (null != cell) {
			HSSFRichTextString richTextString = cell.getRichStringCellValue();
			if (null != richTextString) {
				if (richTextString.getString().trim().equals("")) {
					return null;
				}
				return richTextString.getString().trim();
			}
		}
		return null;
	}

	public static String getCellDateString(HSSFCell cell) {
		if (null != cell) {
			if (cell.getCellType() != HSSFCell.CELL_TYPE_BLANK) {
				Date date = cell.getDateCellValue();
				return DateFormatUtil.toDateString(date);
			}
		}
		return null;
	}

	public static Date getCellDate(String value) {
		if (null != value) {
			try {
				return HSSFDateUtil.getJavaDate(Double.parseDouble(value));
			} catch (Exception e) {
				log.error("value=" + value, e);
			}
		}
		return null;
	}

	public static void setCellString(HSSFCell cell, String string) {
		// cell.setEncoding(HSSFCell.ENCODING_UTF_16);//handle Chinese now
		// automatically handles Unicode without forcing the encoding.
		if (null != cell) {
			HSSFRichTextString value = new HSSFRichTextString(string);
			cell.setCellValue(value);
		}
	}

	private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fillForegroundColor) {
		if (null == workbook) {
			return null;
		}
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setWrapText(true);

		// HSSFFont font = workbook.createFont();
		// font.setFontName(FontFamily.STFangsong);
		// font.setBoldweight((short) 100);
		// font.setFontHeight((short) 200);
		// if fail set cell color
		// font.setColor(HSSFColor.RED.index);

		// cellStyle.setFont(font);
		cellStyle.setFillForegroundColor(fillForegroundColor);// 设置背景色
		cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		return cellStyle;
	}

	public static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, HSSFColor fillForegroundColor) {
		return createCellStyle(workbook, (null == fillForegroundColor) ? HSSFColor.YELLOW.index : fillForegroundColor.getIndex());
	}

	public static HSSFCellStyle createErrorCellStyle(HSSFWorkbook workbook) {
		return createCellStyle(workbook, HSSFColor.YELLOW.index);

	}

	public static HSSFRow firstRowOnFirstSheet(HSSFWorkbook workbook) {
		if (null == workbook) {
			return null;
		}
		HSSFSheet sheet = workbook.getSheetAt(0);
		if (null == sheet) {
			return null;
		}
		return sheet.getRow(0);

	}

	public static HSSFWorkbook createWorkbook(List<List<?>> data) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFCellStyle cellStyle = fomatStyle(workbook, HSSFColor.BLUE.index, HSSFColor.LIGHT_TURQUOISE.index, HSSFCellStyle.ALIGN_LEFT);
		HSSFCellStyle header = fomatStyle(workbook, HSSFColor.RED.index, HSSFColor.YELLOW.index, HSSFCellStyle.ALIGN_CENTER);
		return createWorkbook(data, workbook, header, cellStyle);
	}

	public static void addSheet(List<List<?>> data, HSSFWorkbook workbook) {
		addSheet(data, workbook, null);
	}

	public static void addSheet(List<List<?>> data, HSSFWorkbook workbook, String sheetName) {
		HSSFCellStyle cellStyle = fomatStyle(workbook, HSSFColor.BLUE.index, HSSFColor.LIGHT_TURQUOISE.index, HSSFCellStyle.ALIGN_LEFT);
		HSSFCellStyle headerCell = fomatStyle(workbook, HSSFColor.RED.index, HSSFColor.YELLOW.index, HSSFCellStyle.ALIGN_CENTER);
		addSheet(data, workbook, headerCell, cellStyle, sheetName);
	}

	public static void addSheet(List<List<?>> data, HSSFWorkbook workbook, HSSFCellStyle headerCell, HSSFCellStyle cellStyle, String sheetName) {
		HSSFSheet sheet;
		if (sheetName == null) {
			sheet = workbook.createSheet();
		} else {
			sheet = workbook.createSheet(sheetName);
		}
		if (data == null || data.isEmpty()) {
			return;
		}
		// 表头
		HSSFRow columnRow = sheet.createRow(0);
		List<?> columns = data.get(0);
		for (Object v : columns) {
			createMergedRegion2Cell(sheet, columnRow, v, headerCell);
		}
		// 数据
		for (int i = 1; i < data.size(); i++) {
			HSSFRow row = sheet.createRow(i);
			List<?> rowData = data.get(i);
			for (Object v : rowData) {
				createMergedRegion2Cell(sheet, row, v, cellStyle);
			}
		}
	}

	public static HSSFWorkbook createWorkbook(List<List<?>> data, HSSFWorkbook workbook, HSSFCellStyle headerCell, HSSFCellStyle cellStyle) {
		if (data == null || data.isEmpty()) {
			return workbook;
		}
		addSheet(data, workbook);
		return workbook;
	}

	public static void createRow(HSSFRow row, List<Object> rowValues, HSSFCellStyle cellStyle) {
		for (Object v : rowValues) {
			createCell(row, v, cellStyle);
		}
	}

	@SuppressWarnings("unchecked")
	public static void createMergedRegion2Cell(HSSFSheet sheet, HSSFRow row, Object value, HSSFCellStyle cellStyle) {
		if (value instanceof Map) {
			Map<String, Object> mapValue = (Map<String, Object>) value;
			Object colspan = mapValue.get("colspan");
			createCell(row, mapValue.get("value"), cellStyle);
			if (colspan instanceof Integer) {
				int rowFrom = sheet.getLastRowNum();
				int rowTo = sheet.getLastRowNum();
				short colFrom = sheet.getRow(sheet.getLastRowNum()).getLastCellNum();
				short colTo = (short) (colFrom + ((Integer) colspan).shortValue());

				Region region = new Region(rowFrom, colFrom, rowTo, colTo);

				sheet.addMergedRegion(region);
				for (int i = colFrom; i < colTo; i++) {
					createCell(row, "", cellStyle);
				}

			}
		} else {
			createCell(row, value, cellStyle);
		}

	}

	public static void createCell(HSSFRow row, Object value, HSSFCellStyle cellStyle) {
		HSSFCell cell = row.createCell((short) (row.getLastCellNum() + 1));
		String v = String.valueOf(value);
		if (v == null) {
			v = "";
		}
		HSSFRichTextString valueString = new HSSFRichTextString(v);
		cell.setCellValue(valueString);
		cell.setCellStyle(cellStyle);

	}

	public static HSSFCellStyle fomatStyle(HSSFWorkbook workbook, short fontColor, short cellColor, short align) {
		// 创建字体样式
		HSSFFont font = workbook.createFont();
		// font.setFontName("Verdana");
		// font.setFontName("宋体");
		font.setFontName("SimSun");
		font.setBoldweight((short) 100);
		font.setFontHeight((short) 200);
		font.setColor(fontColor);

		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(align);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setFillForegroundColor(cellColor);
		style.setFillBackgroundColor(cellColor);
		// 自动换行
		style.setWrapText(true);

		// 设置边框
		style.setBottomBorderColor(cellColor);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);

		// 设置字体
		style.setFont(font);
		return style;
	}

	public static void main(String args[]) {
		Double db = new Double(10000);
		Double db1 = new Double(100000);
		Double db2 = new Double(9999999);
		Double db3 = new Double(10000000);
		Double db4 = new Double(100000000);
		Double db5 = new Double(1000000000);
		db.getClass();
	}

}
